Create Staging Table in Staging Database and Populate the Staging Tables 10

Download the SQL File attached to the  

Run Script that is attached to create your Staging tables in the Staging Database. Once ran, the tables will be made up of these columns.


*Using SSIS Toolbox, Create "LoadStagingTable" Package, the following components will be drag in the Control Flow Dashboard;




C# Code for Script Task " GetJSONFileName "

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
		public void Main()
		{
            // TODO: Add your code here
            string filepath;
            string filename;

            filepath = Dts.Variables["User::JSONFilePath"].Value.ToString();

            filename = Path.GetFileName(filepath);

            Dts.Variables["User::JSONFileName"].Value = filename;

            // MessageBox.Show(filename);


            Dts.TaskResult = (int)ScriptResults.Success;
		}




C# Code for Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace IncidentsNameSpace
{
    class Incidents
    {
        public string IncidentNumber { get; set; }
        public string IncidentType { get; set; }
        public string IncidentDate { get; set; }
        public string IncidentLocation { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string ReportingDistrict { get; set; }
        public string GeoLocation { get; set; }
        public string HandlingUnitID { get; set; }
        public string HandlingUnitName { get; set; }
        public string NumberOfInvolvedDeputies { get; set; }
        public string DeputyRace { get; set; }
        public string NumberOfSuspects { get; set; }
        public string SuspectRace { get; set; }
        public string NumberOfSuspectWounded { get; set; }
        public string NumberOfSuspectDeceased { get; set; }
        public string WeaponInvolvedCategory { get; set; }
        public string WeaponInvolvedCategoryDesc { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }

    }
}
C# Code for Main
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.IO;
using IncidentsNameSpace;

#endregion
    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        String jsonFileContent = File.ReadAllText(@"C: \Users\m_kol\Documents\DWH\12.Project1\MyProject\Json File\20201114.JSON");
        JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };
        List<Incidents> IncidentDetails = js.Deserialize<List<Incidents>>(jsonFileContent);
        foreach (Incidents IncidentList in IncidentDetails)
        {
            Output0Buffer.AddRow();
            Output0Buffer.IncidentNumber = IncidentList.IncidentNumber;
            Output0Buffer.IncidentType = IncidentList.IncidentType;
            Output0Buffer.IncidentDate = IncidentList.IncidentDate;
            Output0Buffer.IncidentLocation = IncidentList.IncidentLocation;
            Output0Buffer.City = IncidentList.City;
            Output0Buffer.State = IncidentList.State;
            Output0Buffer.Zip = IncidentList.Zip;
            Output0Buffer.ReportingDistrict = IncidentList.ReportingDistrict;
            Output0Buffer.GeoLocation = IncidentList.GeoLocation;
            Output0Buffer.HandlingUnitID = IncidentList.HandlingUnitID;
            Output0Buffer.HandlingUnitName = IncidentList.HandlingUnitName;
            Output0Buffer.NumberOfInvolvedDeputies = IncidentList.NumberOfInvolvedDeputies;
            Output0Buffer.DeputyRace = IncidentList.DeputyRace;
            Output0Buffer.NumberOfSuspects = IncidentList.NumberOfSuspects;
            Output0Buffer.SuspectRace = IncidentList.SuspectRace;
            Output0Buffer.NumberOfSuspectWounded = IncidentList.NumberOfSuspectWounded;
            Output0Buffer.NumberOfSuspectDeceased = IncidentList.NumberOfSuspectDeceased;
            Output0Buffer.WeaponInvolvedCategory = IncidentList.WeaponInvolvedCategory;
            Output0Buffer.WeaponInvolvedCategoryDesc = IncidentList.WeaponInvolvedCategoryDesc;
            Output0Buffer.Latitude = IncidentList.Latitude;
            Output0Buffer.Longitude = IncidentList.Longitude;

        }

    }
}




Expression Codes For Derived Column Transformation

FileDate  SUBSTRING(@[User::JSONFileName],5,2) + "/" + SUBSTRING(@[User::JSONFileName],7,2) + "/" + SUBSTRING(@[User::JSONFileName],1,4)
DeputyRace	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DeputyRace,"ASIAN-PACIFIC,ISL","A"),"FILIPINO","F"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")
SuspectRace	REPLACENULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A") == "" ? NULL(DT_WSTR,255) : ((DT_WSTR,255)REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")),"UNKNOWN")





Introduction to c# Scripting in SSIS

Script Task in SSIS

Script Component in SSIS